package springboot.study.project.util.db;

import springboot.study.project.util.db.druid.Druid;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Java中使用JDBC连接数据库 1） 加载驱动 2） 创建数据库连接 3） 创建执行sql的语句 4） 执行语句 5） 处理执行结果 6） 释放资源
 *
 * @author liu.hb
 */
public class DBUtil {
    private static String url = null;
    private static String username = null;
    private static String password = null;
    private static String driverClass = null;
    private static final String SQL = "SELECT * FROM "; // 数据库操作
    private static final String SQL_COUNT = "SELECT count(1) as count FROM "; // 数据库操作
    private static final Logger logger = LoggerFactory.getLogger(DBUtil.class); // 日志打印

    static {
        try {
            // 在 java 项目下，可以使用以下方式读取 配置文件；但是，在 web 项目下不可以；
            // FileInputStream in = new FileInputStream("./src/db.properties");

            /**
             * 还有一种加载配置的方式：使用 类路径的方式 加载配置文件，如下所示： /：斜杠表示 classpath 的根目录； web 项目中的 根目录 从
             * WEB-INF/classes 目录开始； java 项目中的 classpath 根目录从 bin 目录开始； 这种方式是通用的，不管是在 java
             * 项目中，还是在 web 项目中；
             */
            InputStream in = DBUtil.class.getResourceAsStream("/db.properties");

            // 加载配置文件
            Properties prop = new Properties();
            prop.load(in);

            // 读取配置文件
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");
            driverClass = prop.getProperty("driverClass");

            // 注册驱动程序
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取 数据库连接对象
     */
    public static Connection getConnection() {
        try {
            // 获取数据库连接对象
            Connection conn = Druid.getConnection(); // 德鲁伊连接池
            if (conn == null) {
                conn = DriverManager.getConnection(url, username, password); // JDBC方式
            }
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * 释放资源：后创建的先释放
     *
     * @param conn Connection 对象
     * @param stmt PreparedStatement 对象
     * @param rs   ResultSet 对象
     */
    public static void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 获取数据库下的所有表名称（方式一）
     */
    public static List<String> getTableNames() {
        List<String> tables = new ArrayList();
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        // 正则校验表名（只允许出现字母数字下划线）
        String patternMode = "^[0-9a-zA-Z_]{1,}$";
        Pattern pattern = Pattern.compile(patternMode);
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SHOW TABLES ");
            while (rs.next()) {
                String tableName = rs.getString(1).toUpperCase();
                Matcher matcher = pattern.matcher(tableName);
                if (matcher.find()) {
                    tables.add(tableName);
                }
            }
        } catch (SQLException e) {
            logger.error("getTableNames failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }
        return tables;
    }

    /**
     * 获取数据库下的所有表名（方式二）
     */
    public static List<String> getTableNames2() {
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            // 获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            // 从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3).toUpperCase());
            }
        } catch (SQLException e) {
            logger.error("getTableNames failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, null, rs);
        }
        return tableNames;
    }

    /**
     * 根据表名获得某表注释
     */
    public static String getCommentByTableName(String tableName) {
        String tableComment = "";

        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
            if (rs != null && rs.next()) {
                String createDDL = rs.getString(2);
                tableComment = parse(createDDL);
            }
        } catch (SQLException e) {
            logger.error("getCommentByTableName failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }

        return tableComment;
    }

    /**
     * 根据表名集合获得某表注释
     */
    public static Map<String, String> getCommentByTableName(List<String> tableName) {
        Map resultMap = new HashMap();

        Connection conn = getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            for (int i = 0; i < tableName.size(); i++) {
                String table = (String) tableName.get(i);
                ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + table);
                if (rs != null && rs.next()) {
                    String createDDL = rs.getString(2);
                    String comment = parse(createDDL);
                    resultMap.put(table.toUpperCase(), comment);
                }
                closeConnection(null, null, rs);
            }
        } catch (SQLException e) {
            logger.error("getCommentByTableName failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }

        return resultMap;
    }

    /**
     * 获取表中所有字段名称
     */
    public static List<String> getColumnNames(String tableName) {
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;

        List<String> columnNames = new ArrayList<>();
        try {
            pStemt = conn.prepareStatement(tableSql);
            // 结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            // 表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1).toUpperCase());
            }
        } catch (SQLException e) {
            logger.error("getColumnNames failure, tableName: " + tableName, e);
            e.printStackTrace();
        }

        closeConnection(conn, pStemt, null);
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     */
    public static List<String> getColumnTypes(String tableName) {
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;

        List<String> columnTypes = new ArrayList<>();
        try {
            pStemt = conn.prepareStatement(tableSql);
            // 结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            // 表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            logger.error("getColumnTypes failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, pStemt, null);
        }

        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     */
    public static List<String> getColumnComments(String tableName) {
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        ResultSet rs = null;

        List<String> columnComments = new ArrayList<>();// 列名注释集合
        try {
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            logger.error("getColumnComments failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, pStemt, rs);
        }

        return columnComments;
    }

    /**
     * 获取表字段以及字段类型（key:字段名，value:字段类型）
     */
    public static Map<String, String> getColumnNameAndType(String tableName) {
        Connection conn = getConnection();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;

        Map<String, String> columnNameAndType = new HashMap<String, String>();
        try {
            pStemt = conn.prepareStatement(tableSql);
            // 结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            // 表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                String name = rsmd.getColumnName(i + 1).toUpperCase();
                String type = rsmd.getColumnTypeName(i + 1).toUpperCase();
                columnNameAndType.put(name, type);
            }
        } catch (SQLException e) {
            logger.error("getColumnNameAndType failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, pStemt, null);
        }
        return columnNameAndType;
    }

    /**
     * 获取表总记录条数
     */
    public static String getTableRecordCount(String tableName) {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        String tableRecordCount = "";
        try {
            stmt = conn.createStatement();

            rs = stmt.executeQuery(SQL_COUNT + tableName);
            if (rs != null && rs.next()) {
                tableRecordCount = rs.getString("count");
            }
        } catch (SQLException e) {
            logger.error("getTableRecordCount failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }

        return tableRecordCount;
    }

    /**
     * 判断数据库中是含有此表（不分大小写）
     */
    public static boolean hasTable(String tableName) {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        boolean resulrt = false;
        try {
            stmt = conn.createStatement();
            DatabaseMetaData mete = conn.getMetaData();

            rs = mete.getTables(null, null, tableName, null);
            if (rs != null && rs.next()) {
                resulrt = true;
            }
        } catch (SQLException e) {
            logger.error("hasTable failure", e);
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }

        return resulrt;
    }

    /**
     * 返回注释信息
     */
    public static String parse(String all) {
        String comment = null;
        int index = all.indexOf("COMMENT='");
        if (index < 0) {
            return "";
        }
        comment = all.substring(index + 9);
        comment = comment.substring(0, comment.length() - 1);
        return comment;
    }

    /**
     * 获取表字段Map
     */
    public static Map<String, Map<String, Object>> getAllTableFieldMap() {
        Map<String, Map<String, Object>> tableFieldMap = new HashMap<>();

        // 获取该数据库下所有表名
        List<String> tableNames = DBUtil.getTableNames();

        // 遍历循环，获取字段，添加到Map
        for (String tableName : tableNames) {
            List<String> fieldNames = DBUtil.getColumnNames(tableName);
            // 创建属于该表的字段Map
            Map<String, Object> fieldMap = new HashMap<>();
            for (String fieldName : fieldNames) {
                fieldMap.put(fieldName, null);
            }
            tableFieldMap.put(tableName, fieldMap);
        }

        return tableFieldMap;
    }

    public static void main(String[] args) {
//        List<String> tableNames = getTableNames();
//        System.out.println("tableNames:" + tableNames);
//        for (String tableName : tableNames) {
//            System.out.println("================start==========================");
//            System.out.println("==============================================");
//            System.out.println("ColumnNames:" + getColumnNames(tableName));
//            System.out.println("ColumnTypes:" + getColumnTypes(tableName));
//            System.out.println("ColumnComments:" + getColumnComments(tableName));
//            System.out.println("ColumnNameAndType: " + getColumnNameAndType(tableName));
//            System.out.println("==============================================");
//            System.out.println("=================end=======================");
//        }

        Map<String, Map<String, Object>> map = getAllTableFieldMap();
        System.out.println(hasTable("DIA_bs_ex_azpsp_d4500"));
    }
}
